package com.cy.model;

import com.cy.BasicModel;
import com.cy.ext.plugin.tablebind.TableBind;
import com.cy.security.Encryption;
import com.cy.utils.Digests;
import com.cy.utils.Encodes;
import com.google.common.collect.Lists;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import org.apache.shiro.crypto.hash.Sha512Hash;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.cy.utils.SimpleDateUtils.getMaxTime;
import static com.cy.utils.SimpleDateUtils.getMinTime;
import static com.cy.utils.SimpleSqlUtils.generateSql;
import static com.cy.utils.SimpleSqlUtils.in;
import static org.apache.commons.lang3.StringUtils.isNotBlank;

@TableBind(tableName="user",pkName = "user_id")
public class User extends BasicModel<User> {
    public static final User dao = new User();
    public static final String SELECT_ALL = "`user_id`,`account`,`mobile`,`password`,`salt`,`nick_name`,`real_name`,`email`,`sex`,`level`,`remark`,`create_time`,`status`,`province`,`city`,`district`,`address`";
    public static final int HASH_INTERATIONS = 264544;
    private static final int SALT_SIZE = 32;

    /**
     * 模糊查询账号
     */
    public List<String> findMobileFuzzily(String fuzzilyMobile) {
        return Db.query("SELECT mobile FROM user WHERE mobile LIKE ?", fuzzilyMobile);
    }

    @Override
    public Page<User> paginate(Integer pageNumber, Integer pageSize, String column, User model) {
        List<Object> paras = new ArrayList<>();
        StringBuffer sqlExceptSelectBuffer = new StringBuffer("FROM (((user u LEFT JOIN user_map_company umc ");
        sqlExceptSelectBuffer.append("ON u.user_id = umc.user_id) LEFT JOIN company c ");
        sqlExceptSelectBuffer.append("ON umc.company_id = c.id) LEFT JOIN user_map_role umr ");
        sqlExceptSelectBuffer.append("ON u.user_id = umr.user_id) LEFT JOIN role_label r ");
        sqlExceptSelectBuffer.append("ON r.role_id = umr.role_id ");
        StringBuffer selectBuffer = new StringBuffer("SELECT ").append(column);

        pageNumber = pageNumber == null || pageNumber == 0 ? 1 : ++pageNumber;
        pageSize = pageSize == null || pageSize == 0 ? 20 : pageSize;

        if (model != null) {
            int i = 0;
            String[] attrNames = model.getAttrNames();
            for (String attrName : attrNames) {
                Object attrValue = model.get(attrName);
                if (attrValue != null) {
                    sqlExceptSelectBuffer.append(i++ == 0 ? " WHERE " : " AND ");
                    sqlExceptSelectBuffer.append("`" + attrName + "`=?");
                    paras.add(attrValue);
                }
            }
        }

        sqlExceptSelectBuffer.append(" GROUP BY u.user_id");
        return paras.size() > 0 ? paginate(pageNumber, pageSize, selectBuffer.toString(), sqlExceptSelectBuffer.toString(), paras.toArray())
                                : paginate(pageNumber, pageSize, selectBuffer.toString(), sqlExceptSelectBuffer.toString());
    }

    public User findByMobile(String moblie){
        return findFirst("select * from user where mobile = ?",moblie);
    }

    public User findByAccount(String account){
        return findFirst("select * from user where account = ?",account);
    }

    @Override
    public boolean save() {
        entryptPassword();
        if (getStatus() == null) {
            setStatus(1);
        }
        if (getCreateTime() == null) {
            setCreateTime(new Date());
        }
        return super.save();
    }

    public User getResultUser(){
        User resultUser = new User();
        resultUser.cover(this);
        resultUser.put("user_tn", Encryption.getHashCode(this.getUserId().intValue(), 4));
        resultUser.put("user_id",this.getUserId());
        resultUser.remove("password", "salt");
        return resultUser;
    }

    /**
     * 检查用户数据是否唯一
     *
     * @param mobile 账号
     * @return 是否唯一
     */
    public boolean checkAccountUnique(String mobile) {
        return findByMobile(mobile) != null;
    }

    /**
     * 加密密码
     */
    public void entryptPassword() {
        String plainPassword = getStr("password");
        if (isNotBlank(plainPassword)) {
            byte[] salt = Digests.generateSalt(SALT_SIZE);
            setSalt(Encodes.encodeHex(salt));
            setPassword(entryptPassword(plainPassword, salt));
        }
    }

    private String entryptPassword(String plainPassword, byte[] salt) {
        Sha512Hash sha = new Sha512Hash(plainPassword, salt, HASH_INTERATIONS);
        return sha.toHex();
    }

    /**
     * 对比密码
     *
     * @param password
     * @return
     */
    public boolean matchPassword(String password) {
        String hex_password = entryptPassword(password, Encodes.decodeHex(getSalt()));
        return getPassword().equals(hex_password);
    }

    /**
     * 分页查询用户操作过的设备
     */
    public Page<User> pageDeviceByUser(Integer pageNumber, Integer pageSize, Model<? extends Model<?>> model) {
        List<Object> paras = new ArrayList<>();
        StringBuilder selectBuffer = new StringBuilder(" SELECT u.account, COUNT(1) AS remark_num, MAX(uri.create_time) AS last_remark_time, d.device_id");
        StringBuilder sqlExceptSelectBuffer = new StringBuilder(100);
        sqlExceptSelectBuffer.append(" FROM (user_remark_info uri LEFT JOIN device d");
        sqlExceptSelectBuffer.append(" ON uri.device_id = d.device_id) LEFT JOIN user u");
        sqlExceptSelectBuffer.append(" ON uri.user_id = u.user_id");
        sqlExceptSelectBuffer.append(" WHERE 1 = 1");
        if (model != null) {
            String[] attrNames = model.getAttrNames();
            for (String attrName : attrNames) {
                Object attrValue = model.get(attrName);
                if (attrValue != null) {
                    sqlExceptSelectBuffer.append(" AND d.`" + attrName + "`=? ");
                    paras.add(attrValue);
                }
            }
        }
        sqlExceptSelectBuffer.append(" GROUP BY u.user_id");
        sqlExceptSelectBuffer.append(" ORDER BY last_remark_time DESC");
        pageNumber = pageNumber == null || pageNumber == 0 ? 1 : pageNumber + 1;
        pageSize = pageSize == null || pageSize == 0 ? 20 : pageSize;
        Page<User> devicePage = paras.size() > 0 ? paginate(pageNumber, pageSize, selectBuffer.toString(), sqlExceptSelectBuffer.toString(), paras.toArray())
                : paginate(pageNumber, pageSize, selectBuffer.toString(), sqlExceptSelectBuffer.toString());
        return devicePage;
    }

    /**
     * 按年份统计
     *
     * @param from 从from年
     * @param to 到to年
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByYear(String from, String to, String[] actions, String[] deviceIds){
        List<String> selects = Lists.newArrayList("YEAR(u.create_time) AS xField", "COUNT(DISTINCT u.user_id) AS yField");
        List<String> tables = new ArrayList<>();
        List<String> innerJoin = new ArrayList<>();
        List<String> wheres = new ArrayList<>();
        List<String> groupBy = Lists.newArrayList("YEAR(u.create_time)");
        List<String> orderBy = Lists.newArrayList("u.create_time");

        tables.add("user u");
        for (String action : actions){
            switch (action){
                case "register":
                    if (isNotBlank(from))
                        wheres.add("u.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("u.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    break;
                case "active":
                    selects = Lists.newArrayList("YEAR(el.create_time) AS xField", "COUNT(DISTINCT u.user_id) AS yField");
                    innerJoin.add("u.user_id = el.user_id");
                    tables.add("encryption_log_archived el");
                    if (isNotBlank(from))
                        wheres.add("el.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("el.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("el.device_id", deviceIds));
                    groupBy = Lists.newArrayList("YEAR(el.create_time)");
                    orderBy = Lists.newArrayList("el.create_time");
                    break;
                case "brew":
                    selects = Lists.newArrayList("YEAR(bl.brew_time) AS xField", "COUNT(DISTINCT u.user_id) AS yField");
                    innerJoin.add("u.user_id = bl.user_id");
                    tables.add("brewed_log_archived bl");
                    wheres.add("(bl.brew_type = 1 OR bl.brew_type = 2)");
                    if (isNotBlank(from))
                        wheres.add("bl.brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("bl.brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("bl.device_id", deviceIds));
                    groupBy = Lists.newArrayList("YEAR(bl.brew_time)");
                    orderBy = Lists.newArrayList("bl.brew_time");
                    break;
            }
        }
        List<Record> records = Db.find(generateSql(selects, tables, innerJoin, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(3, 1.0F);
        List<Object> dateList = new ArrayList<>(records.size());//日期
        List<Object> dataTotalList = new ArrayList<>(records.size());//数据合计
        int sum = 0;//用户净增总数
        for (Record record : records){
            dateList.add(record.getInt("xField"));
            dataTotalList.add(record.getLong("yField"));
            sum += record.getLong("yField");
        }
        result.put("xField", dateList);
        result.put("yField", dataTotalList);
        result.put("sum", sum);
        return result;
    }

    /**
     * 按季度统计
     *
     * @param from 从yyyy-MM
     * @param to 到yyyy-MM
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByQuarter(String from, String to, String[] actions, String[] deviceIds){
        List<String> selects = Lists.newArrayList("YEAR(u.create_time) AS year", "QUARTER(u.create_time) AS quarter", "COUNT(DISTINCT u.user_id) AS yField");
        List<String> tables = new ArrayList<>();
        List<String> innerJoin = new ArrayList<>();
        List<String> wheres = new ArrayList<>();
        List<String> groupBy = Lists.newArrayList("YEAR(u.create_time)", "QUARTER(u.create_time)");
        List<String> orderBy = Lists.newArrayList("u.create_time");

        tables.add("user u");
        for (String action : actions){
            switch (action){
                case "register":
                    if (isNotBlank(from))
                        wheres.add("u.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("u.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    break;
                case "active":
                    selects = Lists.newArrayList("YEAR(el.create_time) AS year", "QUARTER(el.create_time) AS quarter", "COUNT(DISTINCT u.user_id) AS yField");
                    innerJoin.add("u.user_id = el.user_id");
                    tables.add("encryption_log_archived el");
                    if (isNotBlank(from))
                        wheres.add("el.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("el.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("el.device_id", deviceIds));
                    groupBy = Lists.newArrayList("YEAR(el.create_time)", "QUARTER(el.create_time)");
                    orderBy = Lists.newArrayList("el.create_time");
                    break;
                case "brew":
                    selects = Lists.newArrayList("YEAR(bl.brew_time) AS year", "QUARTER(bl.brew_time) AS quarter", "COUNT(DISTINCT u.user_id) AS yField");
                    innerJoin.add("u.user_id = bl.user_id");
                    tables.add("brewed_log_archived bl");
                    wheres.add("(bl.brew_type = 1 OR bl.brew_type = 2)");
                    if (isNotBlank(from))
                        wheres.add("bl.brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("bl.brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("bl.device_id", deviceIds));
                    groupBy = Lists.newArrayList("YEAR(bl.brew_time)", "QUARTER(bl.brew_time)");
                    orderBy = Lists.newArrayList("bl.brew_time");
                    break;
            }
        }
        List<Record> records = Db.find(generateSql(selects, tables, innerJoin, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(3, 1.0F);
        List<Object> dateList = new ArrayList<>();//日期
        List<Object> dataTotalList = new ArrayList<>();//数据合计
        int sum = 0;//用户净增总数
        for (Record record : records){
            dateList.add(record.getInt("year") +"-Q"+ record.getInt("quarter"));
            dataTotalList.add(record.getLong("yField"));
            sum += record.getLong("yField");
        }
        result.put("xField", dateList);
        result.put("yField", dataTotalList);
        result.put("sum", sum);
        return result;
    }

    private void groupBy(String[] actions){

    }

    /**
     * 按月份统计
     *
     * @param from 从yyyy-MM
     * @param to 到yyyy-MM
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByMonth(String from, String to, String[] actions, String[] deviceIds){
        List<String> selects = Lists.newArrayList("DATE_FORMAT(u.create_time,'%Y-%m') AS xField", "COUNT(DISTINCT u.user_id) AS yField");
        List<String> tables = new ArrayList<>();
        List<String> innerJoin = new ArrayList<>();
        List<String> wheres = new ArrayList<>();
        List<String> groupBy = Lists.newArrayList("YEAR(u.create_time)", "MONTH(u.create_time)");
        List<String> orderBy = Lists.newArrayList("u.create_time");

        tables.add("user u");
        for (String action : actions){
            switch (action){
                case "register":
                    if (isNotBlank(from))
                        wheres.add("u.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("u.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    break;
                case "active":
                    selects = Lists.newArrayList("DATE_FORMAT(el.create_time,'%Y-%m') AS xField", "COUNT(DISTINCT u.user_id) AS yField");
                    innerJoin.add("u.user_id = el.user_id");
                    tables.add("encryption_log_archived el");
                    if (isNotBlank(from))
                        wheres.add("el.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("el.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("el.device_id", deviceIds));
                    groupBy = Lists.newArrayList("YEAR(el.create_time)", "MONTH(el.create_time)");
                    orderBy = Lists.newArrayList("el.create_time");
                    break;
                case "brew":
                    selects = Lists.newArrayList("DATE_FORMAT(bl.brew_time,'%Y-%m') AS xField", "COUNT(DISTINCT u.user_id) AS yField");
                    innerJoin.add("u.user_id = bl.user_id");
                    tables.add("brewed_log_archived bl");
                    wheres.add("(bl.brew_type = 1 OR bl.brew_type = 2)");
                    if (isNotBlank(from))
                        wheres.add("bl.brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("bl.brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("bl.device_id", deviceIds));
                    groupBy = Lists.newArrayList("YEAR(bl.brew_time)", "MONTH(bl.brew_time)");
                    orderBy = Lists.newArrayList("bl.brew_time");
                    break;
            }
        }

        List<Record> records = Db.find(generateSql(selects, tables, innerJoin, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(3, 1.0F);
        List<Object> dateList = new ArrayList<>();//日期
        List<Object> dataTotalList = new ArrayList<>();//数据合计
        int sum = 0;//用户净增总数
        for (Record record : records){
            dateList.add(record.getStr("xField"));
            dataTotalList.add(record.getLong("yField"));
            sum += record.getLong("yField");
        }
        result.put("xField", dateList);
        result.put("yField", dataTotalList);
        result.put("sum", sum);
        return result;
    }

    /**
     * 按周统计
     *
     * @param from 从yyyy-MM-dd
     * @param to 到yyyy-MM-dd
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByWeek(String from, String to, String[] actions, String[] deviceIds){
        List<String> selects = Lists.newArrayList("YEAR(u.create_time) AS year", "WEEKOFYEAR(u.create_time) AS week", "COUNT(DISTINCT u.user_id) AS yField");
        List<String> tables = new ArrayList<>();
        List<String> innerJoin = new ArrayList<>();
        List<String> wheres = new ArrayList<>();
        List<String> groupBy = Lists.newArrayList("YEAR(u.create_time)","WEEKOFYEAR(u.create_time)");
        List<String> orderBy = Lists.newArrayList("u.create_time");

        tables.add("user u");
        for (String action : actions){
            switch (action){
                case "register":
                    if (isNotBlank(from))
                        wheres.add("u.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("u.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    break;
                case "active":
                    selects = Lists.newArrayList("YEAR(el.create_time) AS year", "WEEKOFYEAR(el.create_time) AS week", "COUNT(DISTINCT u.user_id) AS yField");
                    innerJoin.add("u.user_id = el.user_id");
                    tables.add("encryption_log_archived el");
                    if (isNotBlank(from))
                        wheres.add("el.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("el.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("el.device_id", deviceIds));
                    groupBy = Lists.newArrayList("YEAR(el.create_time)","WEEKOFYEAR(el.create_time)");
                    orderBy = Lists.newArrayList("el.create_time");
                    break;
                case "brew":
                    selects = Lists.newArrayList("YEAR(bl.brew_time) AS year", "WEEKOFYEAR(bl.brew_time) AS week", "COUNT(DISTINCT u.user_id) AS yField");
                    innerJoin.add("u.user_id = bl.user_id");
                    tables.add("brewed_log_archived bl");
                    wheres.add("(bl.brew_type = 1 OR bl.brew_type = 2)");
                    if (isNotBlank(from))
                        wheres.add("bl.brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("bl.brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("bl.device_id", deviceIds));
                    groupBy = Lists.newArrayList("YEAR(bl.brew_time)","WEEKOFYEAR(bl.brew_time)");
                    orderBy = Lists.newArrayList("bl.brew_time");
                    break;
            }
        }
        List<Record> records = Db.find(generateSql(selects, tables, innerJoin, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(3, 1.0F);
        List<Object> dateList = new ArrayList<>();//日期
        List<Object> dataTotalList = new ArrayList<>();//数据合计
        int sum = 0;//用户净增总数
        for (Record record : records){
            dateList.add(record.getInt("year") +"-W"+ record.getInt("week"));
            dataTotalList.add(record.getLong("yField"));
            sum += record.getLong("yField");
        }
        result.put("xField", dateList);
        result.put("yField", dataTotalList);
        result.put("sum", sum);
        return result;
    }

    /**
     * 按天统计
     *
     * @param from 从yyyy-MM-dd
     * @param to 到yyyy-MM-dd
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByDay(String from, String to, String[] actions, String[] deviceIds){
        List<String> selects = Lists.newArrayList("DATE_FORMAT(u.create_time,'%Y-%m-%d') AS xField", "COUNT(DISTINCT u.user_id) AS yField");
        List<String> tables = new ArrayList<>();
        List<String> innerJoin = new ArrayList<>();
        List<String> wheres = new ArrayList<>();
        List<String> groupBy = Lists.newArrayList("DATE(u.create_time)");
        List<String> orderBy = Lists.newArrayList("u.create_time");

        tables.add("user u");
        for (String action : actions){
            switch (action){
                case "register":
                    if (isNotBlank(from))
                        wheres.add("u.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("u.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    break;
                case "active":
                    selects = Lists.newArrayList("DATE_FORMAT(el.create_time,'%Y-%m-%d') AS xField", "COUNT(DISTINCT u.user_id) AS yField");
                    innerJoin.add("u.user_id = el.user_id");
                    tables.add("encryption_log_archived el");
                    if (isNotBlank(from))
                        wheres.add("el.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("el.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("el.device_id", deviceIds));
                    groupBy = Lists.newArrayList("DATE(el.create_time)");
                    orderBy = Lists.newArrayList("el.create_time");
                    break;
                case "brew":
                    selects = Lists.newArrayList("DATE_FORMAT(bl.brew_time,'%Y-%m-%d') AS xField", "COUNT(DISTINCT u.user_id) AS yField");
                    innerJoin.add("u.user_id = bl.user_id");
                    tables.add("brewed_log_archived bl");
                    wheres.add("(bl.brew_type = 1 OR bl.brew_type = 2)");
                    if (isNotBlank(from))
                        wheres.add("bl.brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("bl.brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("bl.device_id", deviceIds));
                    groupBy = Lists.newArrayList("DATE(bl.brew_time)");
                    orderBy = Lists.newArrayList("bl.brew_time");
                    break;
            }
        }
        List<Record> records = Db.find(generateSql(selects, tables, innerJoin, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(3, 1.0F);
        List<Object> dateList = new ArrayList<>();//日期
        List<Object> dataTotalList = new ArrayList<>();//数据合计
        int sum = 0;//用户净增总数
        for (Record record : records){
            dateList.add(record.getStr("xField"));
            dataTotalList.add(record.getLong("yField"));
            sum += record.getLong("yField");
        }
        result.put("xField", dateList);
        result.put("yField", dataTotalList);
        result.put("sum", sum);
        return result;
    }

    /**
     * 按小时统计
     * @param from 从yyyy-MM-dd
     * @param to 到yyyy-MM-dd
     * @return Map<String, Object>
     */
    public Map<String,Object> sumByHour(String from, String to, String[] actions, String[] deviceIds) {
        List<String> selects = Lists.newArrayList("HOUR(u.create_time) AS xField", "COUNT(DISTINCT u.user_id) AS yField");
        List<String> tables = new ArrayList<>();
        List<String> innerJoin = new ArrayList<>();
        List<String> wheres = new ArrayList<>();
        List<String> groupBy = Lists.newArrayList("HOUR(u.create_time)");
        List<String> orderBy = Lists.newArrayList("xField");

        tables.add("user u");
        for (String action : actions){
            switch (action){
                case "register":
                    if (isNotBlank(from))
                        wheres.add("u.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("u.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    break;
                case "active":
                    selects = Lists.newArrayList("HOUR(el.create_time) AS xField", "COUNT(DISTINCT u.user_id) AS yField");
                    innerJoin.add("u.user_id = el.user_id");
                    tables.add("encryption_log_archived el");
                    if (isNotBlank(from))
                        wheres.add("el.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("el.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("el.device_id", deviceIds));
                    groupBy = Lists.newArrayList("HOUR(el.create_time)");
                    break;
                case "brew":
                    selects = Lists.newArrayList("HOUR(bl.brew_time) AS xField", "COUNT(DISTINCT u.user_id) AS yField");
                    innerJoin.add("u.user_id = bl.user_id");
                    tables.add("brewed_log_archived bl");
                    wheres.add("(bl.brew_type = 1 OR bl.brew_type = 2)");
                    if (isNotBlank(from))
                        wheres.add("bl.brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("bl.brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("bl.device_id", deviceIds));
                    groupBy = Lists.newArrayList("HOUR(bl.brew_time)");
                    break;
            }
        }
        List<Record> records = Db.find(generateSql(selects, tables, innerJoin, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(4, 1.0F);
        List<Object> dateList = new ArrayList<>(24);//小时
        List<Object> dataTotalList = new ArrayList<>(24);//数据合计
        int sum = 0;//用户净增总数
        for (Record record : records){
            dateList.add(record.get("xField"));
            dataTotalList.add(record.get("yField"));
            sum += (Long)record.get("yField");
        }
        result.put("xField", dateList);
        result.put("yField", dataTotalList);
        result.put("sum", sum);
        return result;
    }

    /**
     * 按年份查询
     *
     * @param from 从from年
     * @param to 到to年
     */
    public List<Record> findByYear(String from, String to, String[] actions, String[] deviceIds){
        List<String> selects = Lists.newArrayList("IFNULL(rmm.province,'其他') AS name", "COUNT(DISTINCT u.user_id) AS value");
        List<String> tables = new ArrayList<>();
        List<String> innerJoin = new ArrayList<>();
        List<String> wheres = new ArrayList<>();
        List<String> groupBy = Lists.newArrayList("rmm.province");

        tables.add("(user u LEFT JOIN region_map_mobile rmm ON u.account = rmm.mobile)");
        for (String action : actions){
            switch (action){
                case "register":
                    if (isNotBlank(from))
                        wheres.add("u.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("u.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    break;
                case "active":
                    tables.add("encryption_log_archived el");
                    innerJoin.add("u.user_id = el.user_id");
                    if (isNotBlank(from))
                        wheres.add("el.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("el.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("el.device_id", deviceIds));
                    break;
                case "brew":
                    tables.add("brewed_log_archived bl");
                    innerJoin.add("u.user_id = bl.user_id");
                    wheres.add("(bl.brew_type = 1 OR bl.brew_type = 2)");
                    if (isNotBlank(from))
                        wheres.add("bl.brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("bl.brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("bl.device_id", deviceIds));
                    break;
            }
        }
        return Db.find(generateSql(selects, tables, innerJoin, wheres, groupBy, null));
    }

    /**
     * 按月份查询
     *
     * @param from 从yyyy-MM
     * @param to 到yyyy-MM
     */
    public List<Record> findByMonth(String from, String to, String[] actions, String[] deviceIds){
        List<String> selects = Lists.newArrayList("IFNULL(rmm.province,'其他') AS name", "COUNT(DISTINCT u.user_id) AS value");
        List<String> tables = new ArrayList<>();
        List<String> innerJoin = new ArrayList<>();
        List<String> wheres = new ArrayList<>();
        List<String> groupBy = Lists.newArrayList("rmm.province");

        tables.add("(user u LEFT JOIN region_map_mobile rmm ON u.account = rmm.mobile)");
        for (String action : actions){
            switch (action){
                case "register":
                    if (isNotBlank(from))
                        wheres.add("u.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("u.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    break;
                case "active":
                    innerJoin.add("u.user_id = el.user_id");
                    tables.add("encryption_log_archived el");
                    if (isNotBlank(from))
                        wheres.add("el.create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("el.create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("el.device_id", deviceIds));
                    break;
                case "brew":
                    innerJoin.add("u.user_id = bl.user_id");
                    tables.add("brewed_log_archived bl");
                    wheres.add("(bl.brew_type = 1 OR bl.brew_type = 2)");
                    if (isNotBlank(from))
                        wheres.add("bl.brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (isNotBlank(to))
                        wheres.add("bl.brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("bl.device_id", deviceIds));
                    break;
            }
        }
        return Db.find(generateSql(selects, tables, innerJoin, wheres, groupBy, null));
    }

    /**
     * 按天查询
     *
     * @param from 从yyyy-MM-dd
     * @param to 到yyyy-MM-dd
     */
    public List<Record> findByDay(String from, String to, String[] actions, String[] deviceIds) {
        List<String> selects = Lists.newArrayList("IFNULL(rmm.province,'其他') AS name", "COUNT(DISTINCT u.user_id) AS value");
        List<String> tables = new ArrayList<>();
        List<String> innerJoin = new ArrayList<>();
        List<String> wheres = new ArrayList<>();
        List<String> groupBy = Lists.newArrayList("rmm.province");

        tables.add("(user u LEFT JOIN region_map_mobile rmm ON u.account = rmm.mobile)");
        for (String action : actions) {
            switch (action) {
                case "register":
                    if (isNotBlank(from))
                        wheres.add("u.create_time >= '" + getMinTime(from, "yyyy-MM-dd HH:mm:ss") + "'");
                    if (isNotBlank(to))
                        wheres.add("u.create_time <= '" + getMaxTime(to, "yyyy-MM-dd HH:mm:ss") + "'");
                    break;
                case "active":
                    innerJoin.add("u.user_id = el.user_id");
                    tables.add("encryption_log_archived el");
                    if (isNotBlank(from))
                        wheres.add("el.create_time >= '" + getMinTime(from, "yyyy-MM-dd HH:mm:ss") + "'");
                    if (isNotBlank(to))
                        wheres.add("el.create_time <= '" + getMaxTime(to, "yyyy-MM-dd HH:mm:ss") + "'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("el.device_id", deviceIds));
                    break;
                case "brew":
                    innerJoin.add("u.user_id = bl.user_id");
                    tables.add("brewed_log_archived bl");
                    wheres.add("(bl.brew_type = 1 OR bl.brew_type = 2)");
                    if (isNotBlank(from))
                        wheres.add("bl.brew_time >= '" + getMinTime(from, "yyyy-MM-dd HH:mm:ss") + "'");
                    if (isNotBlank(to))
                        wheres.add("bl.brew_time <= '" + getMaxTime(to, "yyyy-MM-dd HH:mm:ss") + "'");
                    if (deviceIds != null && deviceIds.length > 0)
                        wheres.add(in("bl.device_id", deviceIds));
                    break;
            }
        }
        return Db.find(generateSql(selects, tables, innerJoin, wheres, groupBy, null));
    }

    public User setUserId(Long userId){
        this.set("user_id", userId);
        return this;
    }

    public Long getUserId(){
        return this. get("user_id");
    }

    public User setMobile(String mobile){
        this.set("mobile",mobile);
        return this;
    }

    public String getMobile(){
        return this.getStr("mobile");
    }

    public User setAccount(String account){
        this.set("account",account);
        return this;
    }

    public String getAccount(){
        return this.getStr("account");
    }

    public User setPassword(String password){
        this.set("password",password);
        return this;
    }

    public String getPassword(){
        return this.getStr("password");
    }

    public User setSalt(String salt){
        this.set("salt",salt);
        return this;
    }

    public String getSalt(){
        return this.getStr("salt");
    }

    public User setNickName(String nickName){
        this.set("nick_name",nickName);
        return this;
    }

    public String getNickName(){
        return this.getStr("nick_name");
    }

    public User setRealName(String realName){
        this.set("real_name",realName);
        return this;
    }

    public String getRealName(){
        return this.getStr("real_name");
    }

    public User setEmail(String email){
        this.set("email",email);
        return this;
    }

    public String getEmail(){
        return this.getStr("email");
    }

    public User setWx(String wx){
        this.set("wx",wx);
        return this;
    }

    public String getWx(){
        return this.getStr("wx");
    }

    public User setPortrait(String portrait){
        this.set("portrait",portrait);
        return this;
    }

    public String getPortrait(){
        return this.getStr("portrait");
    }

    public User setShareCode(String code){
        this.set("share_code",code);
        return this;
    }

    public String getShareCode(){
        return this.getStr("share_code");
    }

    public User setSex(Integer sex){
        this.set("sex",sex);
        return this;
    }

    public Integer getSex(){
        return this. get("sex");
    }

    public User setLevel(Integer level){
        this.set("level",level);
        return this;
    }

    public Integer getLevel(){
        return this. get("level");
    }


    public User setRemark(String remark){
        this.set("remark",remark);
        return this;
    }

    public String getRemark(){
        return this.getStr("remark");
    }

    public User setCreateTime(Date createTime){
        this.set("create_time",createTime);
        return this;
    }

    public Date getCreateTime(){
        return this.get("create_time");
    }
    public User setBirthDay(Date birthDay){
        this.set("birth_day",birthDay);
        return this;
    }

    public Date getBirthDay(){
        return this.get("birth_day");
    }

    public User setStatus(Integer status){
        this.set("status",status);
        return this;
    }

    public Integer getStatus(){
        return this. get("status");
    }

    public User setProvince(String province){
        this.set("province",province);
        return this;
    }

    public String getProvince(){
        return this.getStr("province");
    }

    public User setCity(String city){
        this.set("city",city);
        return this;
    }

    public String getCity(){
        return this.getStr("city");
    }

    public User setDistrict(String district){
        this.set("district",district);
        return this;
    }

    public String getDistrict(){
        return this.getStr("district");
    }

    public User setAddress(String address){
        this.set("address",address);
        return this;
    }

    public String getAddress(){
        return this.getStr("address");
    }

    public List<RoleLabel> getRoles() {
        return RoleLabel.dao.find("SELECT rl.* FROM role_label rl, user_map_role umr WHERE rl.role_id = umr.role_id AND umr.user_id =  ?", getUserId());
    }

}
